/*
 Navicat Premium Data Transfer

 Source Server         : YL
 Source Server Type    : MySQL
 Source Server Version : 80032
 Source Host           : localhost:3306
 Source Schema         : stuinfo

 Target Server Type    : MySQL
 Target Server Version : 80032
 File Encoding         : 65001

 Date: 08/12/2023 16:37:15
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `ClassNo` int NOT NULL AUTO_INCREMENT,
  `ClassName` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `College` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Specialty` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `EnterYear` year NULL DEFAULT NULL,
  PRIMARY KEY (`ClassNo`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '203大数据', '信息工程学院', '大数据技术与应用', 2020);
INSERT INTO `class` VALUES (2, '203人工智能', '信息工程学院', '人工智能', NULL);
INSERT INTO `class` VALUES (3, '203虚拟现实', '信息工程学院', '虚拟现实', 2020);
INSERT INTO `class` VALUES (4, '203高计网', '信息工程学院', '计算机网络', 2020);
INSERT INTO `class` VALUES (5, '203护理', '护理学院', '护理', 2020);
INSERT INTO `class` VALUES (6, '203临床', '医学院', '临床医学', 2020);
INSERT INTO `class` VALUES (7, '203会计', '经济管理学院', '会计', 2020);
INSERT INTO `class` VALUES (8, '203物联网', '信息工程学院', '物联网', 2020);

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `Cno` int NOT NULL AUTO_INCREMENT,
  `Cname` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `Type` set('公共基础课','专业基础课','专业核心课') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '',
  `Credit` decimal(4, 1) NULL DEFAULT NULL,
  `ClassHour` tinyint NULL DEFAULT NULL,
  PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '计算机基础', '公共基础课', 2.0, 36);
INSERT INTO `course` VALUES (2, '大数据导论', '专业基础课', 4.0, 72);
INSERT INTO `course` VALUES (3, 'JAVA高级程序设计', '专业基础课', 6.0, 108);
INSERT INTO `course` VALUES (4, 'MySQL数据库', '专业基础课', 4.0, 72);
INSERT INTO `course` VALUES (5, 'Hadoop大数据开发基础', '专业核心课', 4.0, 72);
INSERT INTO `course` VALUES (6, '数据挖掘', '专业核心课', 4.0, 72);
INSERT INTO `course` VALUES (7, '高等数学', '公共基础课', 2.0, 36);
INSERT INTO `course` VALUES (8, '大学英语', '公共基础课', 2.0, 36);
INSERT INTO `course` VALUES (9, '人工智能导论', '专业基础课', 4.0, 72);
INSERT INTO `course` VALUES (10, '数据结构', '专业基础课', 4.0, 72);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `Sno` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Cno` int NOT NULL,
  `Uscore` decimal(4, 1) NULL DEFAULT NULL,
  `EndScore` decimal(4, 1) NULL DEFAULT NULL,
  PRIMARY KEY (`Sno`, `Cno`) USING BTREE,
  INDEX `FK_cou_sco`(`Cno` ASC) USING BTREE,
  CONSTRAINT `FK_cou_sco` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_stu_sco` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `CK_EndScore` CHECK ((`EndScore` >= 0) and (`EndScore` <= 100)),
  CONSTRAINT `CK_Uscore` CHECK ((`Uscore` >= 0) and (`Uscore` <= 100))
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('2020010001', 2, 87.0, 90.0);
INSERT INTO `score` VALUES ('2020010001', 4, 80.0, 69.0);
INSERT INTO `score` VALUES ('2020010002', 4, 73.0, 86.0);
INSERT INTO `score` VALUES ('2020010003', 4, 69.0, 70.0);
INSERT INTO `score` VALUES ('2020010008', 1, 80.0, 77.0);
INSERT INTO `score` VALUES ('2020010008', 7, 50.0, 66.0);
INSERT INTO `score` VALUES ('2020010008', 8, 78.0, 50.0);
INSERT INTO `score` VALUES ('2020010009', 1, 80.0, 68.0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `Sno` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学号',
  `SName` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `Sex` enum('w','m') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '性别',
  `Nationality` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '民族',
  `Politics` set('群众','党员','共青团员','其他') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '政治面貌',
  `Birth` date NULL DEFAULT NULL COMMENT '出生日期',
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `ClassNo` int NOT NULL COMMENT '班级编号',
  PRIMARY KEY (`Sno`) USING BTREE,
  INDEX `FK_class_stu`(`ClassNo` ASC) USING BTREE,
  CONSTRAINT `FK_class_stu` FOREIGN KEY (`ClassNo`) REFERENCES `class` (`ClassNo`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('2020010001', '刘佳佳', 'w', '汉', '群众', '2002-03-09', '贵州省铜仁市碧江区', 1);
INSERT INTO `student` VALUES ('2020010002', '田露', 'w', '苗', '共青团员', '2001-01-29', '贵州省黔东南苗族侗族自治州', 1);
INSERT INTO `student` VALUES ('2020010003', '汪汪', 'm', '汉', '共青团员', '2003-01-10', '湖南省怀化市', 1);
INSERT INTO `student` VALUES ('2020010004', '刘士加', 'm', '侗', '党员', '2001-07-05', '四川省成都市', 1);
INSERT INTO `student` VALUES ('2020010005', '杨旭', 'm', '汉', '群众', '2002-05-04', '贵州省贵阳市', 1);
INSERT INTO `student` VALUES ('2020010006', '黄云升', 'm', '汉', '党员', '2003-01-02', '贵州省遵义市', 4);
INSERT INTO `student` VALUES ('2020010007', '张强', 'm', '布依族', '群众', '2001-11-02', '贵州省都匀市', 4);
INSERT INTO `student` VALUES ('2020010008', '杨帅帅', 'm', '汉', '共青团员', '2002-12-01', '北京市', 5);
INSERT INTO `student` VALUES ('2020010009', '何楚', 'm', '侗', '共青团员', '2002-08-04', '重庆市', 7);
INSERT INTO `student` VALUES ('2020010010', '刘备', 'm', '汉', '群众', '2001-09-23', '河北省涿州市', 8);

-- ----------------------------
-- View structure for 学生信息
-- ----------------------------
DROP VIEW IF EXISTS `学生信息`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `学生信息` AS select `student`.`Sno` AS `Sno`,`student`.`SName` AS `SName`,`class`.`ClassName` AS `ClassName`,`class`.`Specialty` AS `Specialty`,`class`.`College` AS `College` from (`student` join `class` on((`student`.`ClassNo` = `class`.`ClassNo`)));

SET FOREIGN_KEY_CHECKS = 1;
